﻿using System;
using System.Collections.Generic;
using System.Text;

namespace PS.DAL
{
    /// <summary>
    /// 生成分页sql语句的工具类
    /// </summary>
    class PageSplitHelper
    {
        /// <summary>
        /// 获取读取查询记录条数的sql语句
        /// </summary>
        /// <param name="sql">原始sql语句，注意select from order by asc desc等关键字必须小写</param>
        /// <returns></returns>
        public static string GetTotalSql(string sql)
        {
			if (sql.IndexOf(" top ") < 0)
			{
				string str = sql.Substring(sql.ToLower().IndexOf("from"));

				if (str.IndexOf("order by") >= 0)
				{
					str = str.Substring(0, str.IndexOf("order by"));
				}

				return "select count(*) " + str;
			}
			else
			{
				return "select count(*) from (" + sql + ") a";
			}
        }

        /// <summary>
        /// 获取分页的sql语句
        /// </summary>
        /// <param name="sql">原始sql语句，注意select from order by asc desc等关键字必须小写</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="totalCount">总记录条数</param>
        /// <returns></returns>
        public static string GetPageSql(string sql, long pageSize, long pageIndex,long totalCount)
        {
			if (sql.IndexOf(" top ") > 0)
				return sql;

            string orderBy = sql.Substring(sql.IndexOf("order by") + 9);
            string orderByDesc = "";
            string[] orderFields = orderBy.Split(new char[]{','});
            foreach (string field in orderFields)
            {
                string str = field;

                if (str.IndexOf("asc") >= 0)
                {
                    str = str.Replace("asc", "desc");
                }else
                    if (field.IndexOf("desc") >= 0)
                    {
                        str = str.Replace("desc", "asc");
                    }
                    else
                    {
                        str += " desc";
                    }
                orderByDesc += str + ",";
            }
            orderByDesc = orderByDesc.Substring(0, orderByDesc.Length - 1);

            sql = sql.Insert(7," top " + (pageIndex * pageSize).ToString() + " ");

            StringBuilder sb = new StringBuilder();

            sb.Append("select * from (")
				.Append("select top ").Append(pageSize * pageIndex <= totalCount ? pageSize : totalCount - pageSize * (pageIndex - 1))
                .Append(" * from (").Append(sql)
                .Append(") a order by ").Append(orderByDesc).Append(") a order by ")
                .Append(orderBy);

            return sb.ToString();
        }
    }
}
